Welcome to MUSA 550:
Geospatial Data Science in Python

My day job

  • My name is Nick Hand
  • I am the Director of Finance, Data, and Policy for City Controller Rebecca Rhynhart
  • Goals of our team:
    • Objective, data-driven analysis of financial policies impacting Philadelphia
    • Increasing transparency through data releases and interactive reports

My team covers a range of policy issues in the city:

--> Check out https://controller.phila.gov/policy-analysis

Previously:
Astrophysics Ph.D. at Berkeley


How did I get here?

  • Astrophysics to data science is becoming increasingly common
  • Landing a job through Twitter: https://www.parkingjawn.com
    • Dashboard visualization of monthly parking tickets in Philadelphia
    • Data from OpenDataPhilly

Goal: exploring and extracting insight from complex datasets

Parking Jawn

Class logistics

Lectures

  • Fully remote — all lectures will be recorded and posted to the course's Canvas site
  • 3 hours of lecture material per week
  • Classes will be a mix of lecturing, interactive demos, and in-class lab time
  • Students learning from multiple time zones — trying to find best solution for everyone

Course design

  • Some weeks (mostly earlier in the course) will feature two synchronous 90 minute Zoom classes Tues./Thurs.
  • Other weeks (mostly later in the course) will employ a "flipped" classroom model: a pre-recorded 90 minute lecture will be available at the beginning of the week and the synchronous Thurs. lecture will be lab-based to work through material

Note: there will be an introductory survey for all students and it will ask about the possibility of moving lectures and preferred time slots (this will only occur if a time that works for everyone can be found)

Office Hours

  • Office hours will also be remote and by appointment only
  • Will be available for 2-hour time slot during the week (but will also work with schedules to find times that work)
  • Times are to be determined
  • Course has one teaching assistant: Eugene Chong
    • Email: echong91@upenn.edu
    • Office hours: TBD

Policies

Course Websites

Course has four websites (sorry!). They are:

Each will have its own purpose:

Main course website:

  • Course schedule with links to weekly slides
  • Resources for learning Python, setting up software, and dealing with common issues
  • General course info and policies

Github

  • Github organization set up for the course
  • Each week and assignment will have its own Github repository
  • Assignments will also be submitted through Github

Piazza

  • Will be used for question & answer forum for course materials and assignments
  • Announcements will also be made here so make sure you check frequently or turn on your notifications!
  • Main method of communication will be through Piazza announcements
  • Participation grade will also be determined by user activity on the Piazza forum

Canvas

  • Will be used to host recorded lecture files
  • Will also contain Zoom information for remote lectures via the course calendar
  • Periodically use the discussion board feature as well

Course Website: Highlights

Course Github

The goals of this course

  • Provide students with the knowledge and tools to turn data into meaningful insights and stories
  • Focus on the modern data science tools within the Python ecosystem
  • The pipeline approach to data science:
    • gathering, storing, analyzing, and visualizing data to tell stories
  • Real-world applications of analysis techniques in the urban planning and public policy realm

What we'll cover

Module 1

Exploratory Data Science: Students will be introduced to the main tools needed to get started analyzing and visualizing data using Python

Module 2

Introduction to Geospatial Data Science: Building on the previous set of tools, this module will teach students how to work with geospatial datasets using a range of modern Python toolkits.

Module 3

Data Ingestion & Big Data: Students will learn how to collect new data through web scraping and APIs, as well as how to work effectively with the large datasets often encountered in real-world applications.

Module 4

Geospatial Data Science in the Wild: Armed with the necessary data science tools, students will be introduced to a range of advanced analytic and machine learning techniques using a number of innovative examples from modern researchers.

Module 5

From Exploration to Storytelling: The final module will teach students to present their analysis results using web-based formats to transform their insights into interactive stories.

Assignments and grading

  • Grading:
    • 50% homework
    • 40% final project
    • 10% participation (based on Piazza participation)
  • Late policy: will be accepted late but with a penalty

Homeworks will be assigned (roughly) every two weeks. You must complete five of the seven homework assignments. Four of the assignments are required, and you are allowed to choose the last assignment to complete (out of the remaining three options).

Screen%20Shot%202020-08-31%20at%208.25.12%20PM.png

Screen%20Shot%202020-08-31%20at%208.25.26%20PM.png

Screen%20Shot%202020-08-31%20at%208.25.52%20PM.png

Final Project

The final project is to replicate the pipeline approach on a dataset (or datasets) of your choosing.

Students will be required to use several of the analysis techniques taught in the class and produce a web-based data visualization that effectively communicates the empirical results to a non-technical audience.

More info will be posted here: https://github.com/MUSA-550-Fall-2020/final-project

Any questions so far?

Initial survey

https://www.surveymonkey.com/r/TSVNMGP

Okay, let's get started...

The Incredible Growth of Python

A StackOverflow analysis

The rise of the Jupyter notebook

The engine of collaborative data science

  • First started by a physics grad student around 2001
  • Known as the IPython notebook originally
  • Starting getting popular in ~2011
  • First funding received in 2015 $\rightarrow$ the Jupyter notebook was born

Google searches for Jupyter notebook


Key features

  • Aimed at "computational narratives" — telling stories with data
  • interactive, reproducible, shareable, user-friendly, visualization-focused

Very versatile: good for both exploratory data analysis and polished finished products

Beyond the Jupyter notebook

Google's Colaboratory


  • A fancier notebook experience built on top of Jupyter notebook
  • Running in the cloud on Google's servers
  • An internal Google product that was recently released publicly
  • Very popular for Python-based machine learning
  • Won't need to use much in this course

See https://colab.research.google.com/notebooks/welcome.ipynb

Binder: https://mybinder.org

Screen%20Shot%202020-08-31%20at%208.54.43%20PM.png

Allows you to launch a repository of Jupyter notebooks on GitHub in the cloud

Note: as a free service, it can be a bit slow sometimes

Weekly lectures are available on Binder


Weekly Workflow

  1. Setup local Python environment as part of first homework assignment (week 1)
  2. Follow along on lectures using Binder in the cloud or on your laptop
  3. Work on homeworks locally on your laptop

To follow along today, go to https://github.com/MUSA-550-Fall-2020/week-1

Now to the fun stuff...

These slides are a Jupyter notebook.

A mix of code cells and text cells in Markdown. Change the type of cell in the top menu bar.

In [1]:
# Comments begin with a "#" character in Python
# A simple code cell
# SHIFT-ENTER to execute


x = 10
print(x)

10

Python data types

In [2]:
# integer
a = 10

# float
b = 10.5

# string
c = "this is a test string"

# lists
d = list(range(10))

# booleans 
e = True

# dictionaries
f = {'key1': 1, "key2": 2}
In [3]:
print(a)
print(b)
print(c)
print(d)
print(e)
print(f)

10
10.5
this is a test string
[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
True
{'key1': 1, 'key2': 2}

Alternative method for creating a dictionary

In [4]:
f = dict(key1=1, key2=2, key3=3)

Accessing dictionary values

In [5]:
# access the value with key 'key1'
f['key1']
Out[5]:
1

Accessing list values

In [6]:
# access the second list entry (0 is the first index)
d[1]
Out[6]:
1

Accessing characters of a string

In [7]:
# the first character
c[0]
Out[7]:
't'

Iterators and for loops

In [8]:
# Python code
result = 0
for i in range(100):
    result = result + i
In [9]:
print(result)

4950

Python's inline syntax

In [10]:
a = range(10) # this is an iterator
In [11]:
print(a)

range(0, 10)
In [12]:
# convert it to a list explicitly
a = list(range(10))
print(a)

[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
In [13]:
# or use the INLINE syntax; this is the SAME
a = [i for i in range(10)]
print(a)

[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]

Python functions

def function_name(arg1, arg2, arg3):

    .
    .
    .
    code lines (indented)
    .
    .
    .

    return result
In [14]:
def compute_square(x):
    return x * x
In [15]:
sq = compute_square(5)
print(sq)

25

Keywords: arguments with a default!

In [16]:
def compute_product(x, y=5):
    return x * y
In [17]:
# use the default value for y
print(compute_product(5))

25
In [18]:
# specify a y value other than the default
print(compute_product(5, 10))

50
In [19]:
# can also explicitly tell Python which arguments are which
print(compute_product(5, y=2))
print(compute_product(x=5, y=2))

10
10
In [20]:
# argument names must match the function signature though!
print(compute_product(5, z=5))

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-20-5db83a932315> in <module>
      1 # argument names must match the function signature though!
----> 2 print(compute_product(5, z=5))

TypeError: compute_product() got an unexpected keyword argument 'z'

Getting help in the notebook

Use tab auto-completion and the ? and ?? operators

In [22]:
this_variable_has_a_long_name = 5
In [23]:
# try hitting tab after typing this_ 
#this_
In [24]:
# try typing "r" and then tab
# r
In [25]:
# Forget how to create a range? --> use the help message
range?

Peeking at the source code for a function

Use the ?? operator

In [26]:
compute_product??

The question mark operator gives you access to the help message for any variable or function. I use this frequently and it is the primary method I understand what functions do.

Getting more Python help

This was a very brief introduction. Additional Python tutorials are listed on our course website under "Resources"

https://musa-550-fall-2020.github.io/resources/python/

Screen%20Shot%202020-08-31%20at%209.07.04%20PM-2.png

Recommend tutorial for students with little Python background:

There are also a few good resources from the Berkeley Data Science Institute:

The Data Science Handbook

The The Python Data Science Handbook is a free, online textbook covering the Python basics needed in this course. In particular, the first four chapters are excellent:

Note that you can click on the "Open in Colab" button for each chapter and run the examples interactively using Google Colab.

One more thing: working outside the notebook

In this class, we will almost exclusively work inside Jupyter notebooks — you'll be writing Python code and doing data analysis directly in the notebook.

The more traditional method of using Python is to put your code into a .py file and execute it via the command line (known as the Anaconda Prompt on Windows or Terminal app on MacOS).

See this section of the Practical Python Programming tutorial for more info.

There is a file called hello_world.py in the repository for week 1. If we execute it, it should print out "Hello, World" to the command line.

Let's try it out.

Notebook tip

You can run terminal commands directly in the Jupyter notebook's "code" cell by starting the line with a "!"

To list all of the files in the current folder (the "current working directory"), use the ls command:

In [27]:
! ls

README.md               hello_world.py          lecture-1b.ipynb
__pycache__             joining_infographic.jpg outline.md
data                    lecture-1.ipynb
environment.yml         lecture-1a.ipynb

We see the hello_world.py file listed. Now let's execute it on the command line by using the python command:

In [28]:
! python hello_world.py

Hello World!

Success!

Code editors

When writing software outside the notebook, it's useful to have an application known as a "code editor". This will provide a nice interface for writing Python code and some even have fancy features, like real-time syntax checking and syntax highlighting.

My recommended option is Visual Studio Code.

Now back to the notebook...

Python Data Analysis: the pandas package

Documentation is available at https://pandas.pydata.org

The following line imports the pandas package:

In [29]:
import pandas as pd

Basic pandas concepts

The primary objects in pandas are the:

  • DataFrame, which is very similar to an Excel spreadsheet, and has rows and named columns
  • Series, which represents a single column of data. A DataFrame contains one or more Series and a name for each Series.

The data frame is a commonly used abstraction for data manipulation. Similar implementations exist in R.

You can think Series objects as fancier versions of Python's built-in list data type

To create a Series object:

In [30]:
# city names and population
city_names = pd.Series(['San Francisco', 'San Jose', 'Sacramento'])
population = pd.Series([852469, 1015785, 485199])
In [31]:
city_names
Out[31]:
0    San Francisco
1         San Jose
2       Sacramento
dtype: object

DataFrame objects can be created by passing a dict mapping string column names to their respective Series.

In [32]:
cities_df = pd.DataFrame({ 'City Name': city_names, 'Population': population })
cities_df
Out[32]:
City Name Population
0 San Francisco 852469
1 San Jose 1015785
2 Sacramento 485199

Note: always try to give your variables meaningful names. It will help immensely when you are trying to debug problems or when you're trying to understand the code you've written months later.

Accessing data

In [33]:
# access columns with a dict-like syntax
cities_df['Population']
Out[33]:
0     852469
1    1015785
2     485199
Name: Population, dtype: int64

Rows can be accessed using Python's syntax for slicing

In [34]:
# list slicing: get the elements with indices 1 and 2 (but NOT 3)
cities_list = ['San Francisco', 'San Jose', 'Sacramento']
cities_list[1:3]
Out[34]:
['San Jose', 'Sacramento']
In [35]:
# slicing data frame rows is very similar!
cities_df[1:3]
Out[35]:
City Name Population
1 San Jose 1015785
2 Sacramento 485199

pandas includes functionality for many different ways of selecting data. See the documentation for many more examples.

Manipulating data

In [36]:
cities_df['Population'].median()
Out[36]:
852469.0

NumPy is a popular toolkit for scientific computing.

pandas Series can be used as arguments to most NumPy functions:

In [37]:
import numpy as np

# calculate the median population value
np.median(cities_df['Population'])
Out[37]:
852469.0

For more complex single-column transformations, you can use Series.apply. It accepts a function that is applied to each value in the Series.

For example, we can find which cities have a population greater than a million:

In [38]:
# define our function
def get_large_cities(population):
    return population > 1e6

large_cities_sel = cities_df['Population'].apply(get_large_cities)
In [39]:
large_cities_sel
Out[39]:
0    False
1     True
2    False
Name: Population, dtype: bool
In [40]:
# add the new computed column to our original data frame
cities_df['Large Cities'] = large_cities_sel

# we can also use lambda (unnamed, inline) functions
cities_df['Small Cities'] = cities_df['Population'].apply(lambda population: population < 1e6)

# print out 
cities_df
Out[40]:
City Name Population Large Cities Small Cities
0 San Francisco 852469 False True
1 San Jose 1015785 True False
2 Sacramento 485199 False True

Data selection

We can select the "large" cities by passing the boolean values to the .loc() function of a DataFrame:

In [41]:
cities_df['Large Cities']
Out[41]:
0    False
1     True
2    False
Name: Large Cities, dtype: bool
In [42]:
cities_df.loc[cities_df['Large Cities']]
Out[42]:
City Name Population Large Cities Small Cities
1 San Jose 1015785 True False

There is a faster, more concise, to achieve this!

In [43]:
cities_df['Population'] > 1e6
Out[43]:
0    False
1     True
2    False
Name: Population, dtype: bool
In [44]:
# this is equivalent to doing 
cities_df.loc[cities_df['Population'] > 1e6]
Out[44]:
City Name Population Large Cities Small Cities
1 San Jose 1015785 True False

An even faster way!

We can use the pandas query function.

The query() function will return a subset of your dataframe based on a string version of the boolean expression.

In [45]:
cities_df.query("Population > 1e6")
Out[45]:
City Name Population Large Cities Small Cities
1 San Jose 1015785 True False

How to remember the specifics of all of these functions?

The documentation is your best friend! Use the question mark operator!

In [46]:
# use the question mark
pd.DataFrame.loc?

Now onto a more interesting example...

Let's load census data on rentals rates from the 2017 Amercian Community Survey.

We've stored the data locally in the comma-separated value (CSV) format. Later in the course, you'll learn how to download them directly using Python.

Use the read_csv() function from pandas. The first argument to read_csv() is the file path to load.

In [47]:
census_df = pd.read_csv("./data/census/ACS_17_1YR_B25003.csv")
In [48]:
census_df.head()
Out[48]:
GEO.id GEO.id2 GEO.display-label HD01_VD01 HD02_VD01 HD01_VD02 HD02_VD02 HD01_VD03 HD02_VD03
0 1600000US0107000 107000 Birmingham city, Alabama 90186 3374 40295 2507 49891 3372
1 1600000US0121184 121184 Dothan city, Alabama 25192 875 14935 763 10257 722
2 1600000US0135896 135896 Hoover city, Alabama 34058 1330 22913 1724 11145 1482
3 1600000US0137000 137000 Huntsville city, Alabama 84750 2687 46677 2948 38073 2680
4 1600000US0150000 150000 Mobile city, Alabama 78145 2673 41297 2323 36848 2325

Select certain columns and rename them:

In [49]:
# columns we want
columns = ['GEO.display-label', 'HD01_VD01', 'HD01_VD03']
census_df = census_df[columns].copy()

# rename columns
census_df.columns = ['City', 'Total', 'Rentals']

census_df.head()
Out[49]:
City Total Rentals
0 Birmingham city, Alabama 90186 49891
1 Dothan city, Alabama 25192 10257
2 Hoover city, Alabama 34058 11145
3 Huntsville city, Alabama 84750 38073
4 Mobile city, Alabama 78145 36848
In [50]:
# Add the rental rate
census_df['Rental Rate'] = census_df['Rentals'] / census_df['Total'] * 100
In [51]:
census_df.head()
Out[51]:
City Total Rentals Rental Rate
0 Birmingham city, Alabama 90186 49891 55.320116
1 Dothan city, Alabama 25192 10257 40.715306
2 Hoover city, Alabama 34058 11145 32.723589
3 Huntsville city, Alabama 84750 38073 44.923894
4 Mobile city, Alabama 78145 36848 47.153369
In [52]:
len(census_df)
Out[52]:
608

We can select the largest cities by population using the Series.isin() function, which checks if each value in the Series is in the specified list.

In [53]:
top_cities_list = [
    "Philadelphia city, Pennsylvania",
    "New York city, New York",
    "Los Angeles city, California",
    "Houston city, Texas",
    "Chicago city, Illinois",
    "Phoenix city, Arizona",
]

# selection =  # which rows are valid
census_df.loc[census_df["City"].isin(top_cities_list)]  # select the valid rows
Out[53]:
City Total Rentals Rental Rate
18 Phoenix city, Arizona 559155 245335 43.876027
94 Los Angeles city, California 1384851 878061 63.404727
275 Chicago city, Illinois 1047695 577430 55.114322
426 New York city, New York 3159674 2127881 67.344954
474 Philadelphia city, Pennsylvania 606142 309279 51.024182
525 Houston city, Texas 837686 479409 57.230155

Alternatively, we could use the query() function. The syntax is a bit different (and I always forget and have to look at the documentation).

In the query() string, you can reference existing variables by prefacing the variable name with an "@" symbol.

For example:

In [54]:
# Get the rows where the City column is in "top_cities_list"
census_df.query("City in @top_cities_list")
Out[54]:
City Total Rentals Rental Rate
18 Phoenix city, Arizona 559155 245335 43.876027
94 Los Angeles city, California 1384851 878061 63.404727
275 Chicago city, Illinois 1047695 577430 55.114322
426 New York city, New York 3159674 2127881 67.344954
474 Philadelphia city, Pennsylvania 606142 309279 51.024182
525 Houston city, Texas 837686 479409 57.230155

An aside about string formatting

Python uses the "%" operator to insert variable values into strings. For example, the file name of the data we want to load for 2017 is:

In [55]:
year_str = "17"
path = "./data/census/ACS_%s_1YR_B25003.csv" % year_str
print(path)

./data/census/ACS_17_1YR_B25003.csv

See this guide on this type of string formatting for more info.

Python f-strings

Python version 3.6 introduced a new and improved string formatting syntax, where the variable that is being inserted can be referenced directly using the variable name.

The variable name should be enclosed in curly braces inside the string:

In [56]:
year_str = "17"
path = f"./data/census/ACS_{year_str}_1YR_B25003.csv"
print(path)

./data/census/ACS_17_1YR_B25003.csv

For more info: see this guide

Now let's load data from 2005 to 2017

Let's use a for loop to loop over each year value from 2005 to 2017

In [57]:
all_rental_data = []
for year in range(2005, 2018):
    
    # The variable year changes for each iteration of the loop
    print(f"Loading data for year = {year}...")
    
    # read data for this year
    year_str = str(year) # convert integer value of "year" to a string
    year_str = year_str[2:] # extract the last two digits of the year string
    rental_df = pd.read_csv(f"./data/census/ACS_{year_str}_1YR_B25003.csv")
     
    # columns we want
    columns = ['GEO.display-label', 'HD01_VD01', 'HD01_VD03']
    rental_df = rental_df[columns]

    # rename columns
    rental_df.columns = ['City', 'Total', 'Rentals']
    
    # calculate the rental rate
    rental_df['Rental Rate'] = rental_df['Rentals'] / rental_df['Total'] * 100
    
    # select the cities we want
    selection = rental_df['City'].isin(top_cities_list)
    top_cities_df = rental_df.loc[selection].copy()
    
    # add a column for the year
    top_cities_df['Year'] = year
    
    # save 
    all_rental_data.append(top_cities_df)

# combine all of the data frames into one along the row axis
all_rental_data = pd.concat(all_rental_data, axis=0)

Loading data for year = 2005...
Loading data for year = 2006...
Loading data for year = 2007...
Loading data for year = 2008...
Loading data for year = 2009...
Loading data for year = 2010...
Loading data for year = 2011...
Loading data for year = 2012...
Loading data for year = 2013...
Loading data for year = 2014...
Loading data for year = 2015...
Loading data for year = 2016...
Loading data for year = 2017...
In [58]:
all_rental_data.head()
Out[58]:
City Total Rentals Rental Rate Year
13 Phoenix city, Arizona 503753 204914 40.677475 2005
75 Los Angeles city, California 1284124 771325 60.066240 2005
218 Chicago city, Illinois 1020605 525620 51.500825 2005
349 New York city, New York 3026196 2023924 66.880136 2005
393 Philadelphia city, Pennsylvania 565433 244792 43.292839 2005
In [59]:
# What about the tail?
all_rental_data.tail()
Out[59]:
City Total Rentals Rental Rate Year
94 Los Angeles city, California 1384851 878061 63.404727 2017
275 Chicago city, Illinois 1047695 577430 55.114322 2017
426 New York city, New York 3159674 2127881 67.344954 2017
474 Philadelphia city, Pennsylvania 606142 309279 51.024182 2017
525 Houston city, Texas 837686 479409 57.230155 2017

Basic plotting: matplotlib

Much more to come next week. For now, we'll use some very simple plotting commands with Python's main plotting libary matplotlib.

Import the library we'll use from matplotlib.

In [62]:
from matplotlib import pyplot as plt

We'll use the plt.plot() function which plots a simple x vs y line.

In [64]:
with plt.style.context('ggplot'):
    
    # Create a figure and axes to plot on
    fig, ax = plt.subplots(figsize=(10,6))
    
    # Loop over each city and plot
    for city in all_rental_data['City'].unique():
        
        print(city)
        
        # Select data for this city
        this_city = all_rental_data['City'] == city
        city_df = all_rental_data.loc[this_city]
        
        # Plot Year vs Rental Rate
        ax.plot(city_df['Year'], city_df['Rental Rate'], label=city, linewidth=4)

# Add a legend
ax.legend(loc=0, ncol=3, fontsize=12)

# Set the y limit
ax.set_ylim(30, 72)

# Add a y label
ax.set_ylabel("Rental Rate in Percent");

Phoenix city, Arizona
Los Angeles city, California
Chicago city, Illinois
New York city, New York
Philadelphia city, Pennsylvania
Houston city, Texas

Note: we used the "ggplot" theme to style our plots. Matplotlib comes with a number of built in styles: see all of the examples here.

From 2005 to 2017, Philadelphia had the largest percent increase

Exercise: can you calculate what the increase was for Philadelphia?

Exercise solution

In [ ]:
In [ ]:

An aside about labels and the index in Pandas

Note how the first row of the above dataframe has an index label of 393. This means we can access it using the .loc[] function like:

In [67]:
philly.loc[393]
Out[67]:
City           Philadelphia city, Pennsylvania
Total                                   565433
Rentals                                 244792
Rental Rate                            43.2928
Year                                      2005
Name: 393, dtype: object

This is different than the .iloc[] function, which also indexes based on the integer value of the row, regardless of what the labels in the index are. For example, the first row of a dataframe can always be accessed using:

In [68]:
philly.iloc[0]
Out[68]:
City           Philadelphia city, Pennsylvania
Total                                   565433
Rentals                                 244792
Rental Rate                            43.2928
Year                                      2005
Name: 393, dtype: object

We can reset the index labels so they range from 0 to the length of the dataframe, using the reset_index() function. For example

In [69]:
philly.reset_index(drop=True)
Out[69]:
City Total Rentals Rental Rate Year
0 Philadelphia city, Pennsylvania 565433 244792 43.292839 2005
1 Philadelphia city, Pennsylvania 554048 231323 41.751437 2006
2 Philadelphia city, Pennsylvania 562384 239363 42.562199 2007
3 Philadelphia city, Pennsylvania 578263 253084 43.766245 2008
4 Philadelphia city, Pennsylvania 569835 258459 45.356814 2009
5 Philadelphia city, Pennsylvania 575413 264129 45.902508 2010
6 Philadelphia city, Pennsylvania 576429 265126 45.994563 2011
7 Philadelphia city, Pennsylvania 579874 277323 47.824700 2012
8 Philadelphia city, Pennsylvania 582528 285430 48.998503 2013
9 Philadelphia city, Pennsylvania 577862 277301 47.987409 2014
10 Philadelphia city, Pennsylvania 581604 275397 47.351291 2015
11 Philadelphia city, Pennsylvania 580205 277707 47.863600 2016
12 Philadelphia city, Pennsylvania 606142 309279 51.024182 2017

Next, to calculate our percent difference we need to get the values for Philadelphia in 2005 and 2017:

In [70]:
# select 2005
philly_2005 = philly.loc[philly['Year']==2005]
value_2005 = philly_2005['Rental Rate'].iloc[0]
print("2005 value = ", value_2005)

2005 value =  43.292839293072745
In [71]:
philly_2005['Rental Rate']
Out[71]:
393    43.292839
Name: Rental Rate, dtype: float64
In [72]:
# select 2017
philly_2017 = philly.loc[philly['Year']==2017]
value_2017 = philly_2017['Rental Rate'].iloc[0]
print("2017 value = ", value_2017)

2017 value =  51.02418245229665
In [73]:
# calculate percent change
percent_change = (value_2017 - value_2005)/ value_2005 * 100
In [74]:
print("change in rental rate in Philadelphia from 2005 to 2017 = ", percent_change)

change in rental rate in Philadelphia from 2005 to 2017 =  17.85824927509661

For more details on the iloc() function, see the documentation on indexing by position.

Now let's compare Zillow rental and home value data

Load citywide Zillow Rent Index (ZRI) and Zillow Home Value Index (ZHVI) data.

Files were downloaded from https://www.zillow.com/research/data/

In [75]:
home_values = pd.read_csv("./data/zillow/Metro_Zhvi_AllHomes.csv", engine='python')
rent_values = pd.read_csv('./data/zillow/Metro_Zri_SingleFamilyResidenceRental.csv', engine='python')

Peek at the first few rows of the ZRI data:

In [76]:
rent_values.head()
Out[76]:
RegionID RegionName SizeRank 2010-11 2010-12 2011-01 2011-02 2011-03 2011-04 2011-05 ... 2018-02 2018-03 2018-04 2018-05 2018-06 2018-07 2018-08 2018-09 2018-10 2018-11
0 102001 United States 0 1250.0 1248.0 1245.0 1240.0 1236.0 1234.0 1236.0 ... 1411 1412 1414 1416 1416 1415 1415 1414 1417 1426
1 394913 New York, NY 1 NaN NaN NaN NaN NaN NaN NaN ... 2422 2417 2417 2418 2417 2416 2415 2415 2425 2438
2 753899 Los Angeles-Long Beach-Anaheim, CA 2 2283.0 2272.0 2263.0 2259.0 2258.0 2263.0 2265.0 ... 2856 2866 2877 2886 2890 2892 2892 2893 2897 2911
3 394463 Chicago, IL 3 1581.0 1569.0 1560.0 1551.0 1541.0 1534.0 1530.0 ... 1698 1696 1696 1696 1695 1694 1694 1693 1697 1713
4 394514 Dallas-Fort Worth, TX 4 1297.0 1297.0 1297.0 1298.0 1298.0 1300.0 1304.0 ... 1624 1622 1622 1621 1619 1618 1618 1616 1620 1630

5 rows × 100 columns

And do the same for the ZHVI data:

In [77]:
home_values.head()
Out[77]:
RegionID RegionName SizeRank 1996-04 1996-05 1996-06 1996-07 1996-08 1996-09 1996-10 ... 2018-02 2018-03 2018-04 2018-05 2018-06 2018-07 2018-08 2018-09 2018-10 2018-11
0 102001 United States 0 100600.0 100600.0 100600.0 100700.0 100800.0 101000.0 101200.0 ... 211500 213200 214400 215600 216800 217900 219100 220200 221600 222800
1 394913 New York, NY 1 165000.0 164800.0 164600.0 164300.0 164100.0 163900.0 163800.0 ... 420800 423400 425300 426600 427600 428600 429500 431100 433500 435700
2 753899 Los Angeles-Long Beach-Anaheim, CA 2 170600.0 170400.0 170100.0 169800.0 169500.0 169300.0 169200.0 ... 635900 639000 640600 641900 643200 644700 646400 647500 648500 650400
3 394463 Chicago, IL 3 138300.0 138900.0 139300.0 139400.0 139300.0 138800.0 138000.0 ... 218000 219300 219900 220500 220900 221100 221500 222100 222800 223600
4 394514 Dallas-Fort Worth, TX 4 115300.0 115400.0 115500.0 115700.0 115900.0 116100.0 116400.0 ... 222600 225500 227000 228400 230200 231700 232600 233800 235900 237800

5 rows × 275 columns

Selecting the cities we want

In [78]:
valid_cities = [
    "New York, NY",
    "Chicago, IL",
    "Los Angeles-Long Beach-Anaheim, CA",
    "Philadelphia, PA",
    "Houston, TX",
    "Phoenix, AZ",
]
In [79]:
selection = home_values['RegionName'].isin(valid_cities)
home_values_trimmed = home_values.loc[selection]
In [80]:
selection = rent_values['RegionName'].isin(valid_cities)
rent_values_trimmed = rent_values.loc[selection]
In [81]:
rent_values_trimmed
Out[81]:
RegionID RegionName SizeRank 2010-11 2010-12 2011-01 2011-02 2011-03 2011-04 2011-05 ... 2018-02 2018-03 2018-04 2018-05 2018-06 2018-07 2018-08 2018-09 2018-10 2018-11
1 394913 New York, NY 1 NaN NaN NaN NaN NaN NaN NaN ... 2422 2417 2417 2418 2417 2416 2415 2415 2425 2438
2 753899 Los Angeles-Long Beach-Anaheim, CA 2 2283.0 2272.0 2263.0 2259.0 2258.0 2263.0 2265.0 ... 2856 2866 2877 2886 2890 2892 2892 2893 2897 2911
3 394463 Chicago, IL 3 1581.0 1569.0 1560.0 1551.0 1541.0 1534.0 1530.0 ... 1698 1696 1696 1696 1695 1694 1694 1693 1697 1713
5 394974 Philadelphia, PA 5 1557.0 1567.0 1572.0 1576.0 1574.0 1574.0 1573.0 ... 1690 1689 1689 1688 1686 1685 1684 1683 1686 1696
6 394692 Houston, TX 6 1370.0 1366.0 1363.0 1361.0 1362.0 1362.0 1363.0 ... 1582 1583 1585 1587 1587 1587 1587 1587 1588 1591
14 394976 Phoenix, AZ 14 1207.0 1209.0 1204.0 1196.0 1188.0 1184.0 1184.0 ... 1394 1397 1401 1403 1402 1399 1397 1396 1402 1423

6 rows × 100 columns

Removing unwanted columns

Unwanted columns can be dropped from the data frame using the drop() function.

In [82]:
unwanted = ['SizeRank', 'RegionID']
home_values_final = home_values_trimmed.drop(unwanted, axis=1)
In [83]:
unwanted = ['SizeRank', 'RegionID']
rent_values_final = rent_values_trimmed.drop(unwanted, axis=1)
In [84]:
rent_values_final
Out[84]:
RegionName 2010-11 2010-12 2011-01 2011-02 2011-03 2011-04 2011-05 2011-06 2011-07 ... 2018-02 2018-03 2018-04 2018-05 2018-06 2018-07 2018-08 2018-09 2018-10 2018-11
1 New York, NY NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 2422 2417 2417 2418 2417 2416 2415 2415 2425 2438
2 Los Angeles-Long Beach-Anaheim, CA 2283.0 2272.0 2263.0 2259.0 2258.0 2263.0 2265.0 2268.0 2270.0 ... 2856 2866 2877 2886 2890 2892 2892 2893 2897 2911
3 Chicago, IL 1581.0 1569.0 1560.0 1551.0 1541.0 1534.0 1530.0 1526.0 1526.0 ... 1698 1696 1696 1696 1695 1694 1694 1693 1697 1713
5 Philadelphia, PA 1557.0 1567.0 1572.0 1576.0 1574.0 1574.0 1573.0 1575.0 1577.0 ... 1690 1689 1689 1688 1686 1685 1684 1683 1686 1696
6 Houston, TX 1370.0 1366.0 1363.0 1361.0 1362.0 1362.0 1363.0 1362.0 1364.0 ... 1582 1583 1585 1587 1587 1587 1587 1587 1588 1591
14 Phoenix, AZ 1207.0 1209.0 1204.0 1196.0 1188.0 1184.0 1184.0 1187.0 1190.0 ... 1394 1397 1401 1403 1402 1399 1397 1396 1402 1423

6 rows × 98 columns

Wide vs long format for datasets

Currently, our data is in wide format $\rightarrow$ each observation has its own column. This usually results in many columns but few rows.

In [85]:
home_values_final
Out[85]:
RegionName 1996-04 1996-05 1996-06 1996-07 1996-08 1996-09 1996-10 1996-11 1996-12 ... 2018-02 2018-03 2018-04 2018-05 2018-06 2018-07 2018-08 2018-09 2018-10 2018-11
1 New York, NY 165000.0 164800.0 164600.0 164300.0 164100.0 163900.0 163800.0 163800.0 164000.0 ... 420800 423400 425300 426600 427600 428600 429500 431100 433500 435700
2 Los Angeles-Long Beach-Anaheim, CA 170600.0 170400.0 170100.0 169800.0 169500.0 169300.0 169200.0 169200.0 169400.0 ... 635900 639000 640600 641900 643200 644700 646400 647500 648500 650400
3 Chicago, IL 138300.0 138900.0 139300.0 139400.0 139300.0 138800.0 138000.0 137000.0 136100.0 ... 218000 219300 219900 220500 220900 221100 221500 222100 222800 223600
5 Philadelphia, PA 109700.0 109700.0 109600.0 109500.0 109500.0 109400.0 109400.0 109400.0 109500.0 ... 225500 226100 226400 226800 227400 228100 228700 229400 230300 231100
6 Houston, TX NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 194300 195800 196800 197900 199000 199700 200400 201200 202200 203200
14 Phoenix, AZ 104800.0 105400.0 106100.0 106700.0 107300.0 107900.0 108500.0 109000.0 109600.0 ... 248700 250300 251600 252900 254500 256100 257300 258600 260500 262000

6 rows × 273 columns

Usually it's better to have data in tidy (also known as long) format.

Tidy datasets are arranged such that each variable is a column and each observation is a row.

In our case, we want to have a column called ZRI and one called ZHVI and a row for each month that the indices were measured.

pandas provides the melt() function for converting from wide formats to tidy formats.

In [86]:
pd.melt?

Now, let's melt our datasets:

In [87]:
ZHVI = pd.melt(
    home_values_final, id_vars=["RegionName"], value_name="ZHVI", var_name="Date"
)
ZRI = pd.melt(
    rent_values_final, id_vars=["RegionName"], value_name="ZRI", var_name="Date"
)

and take a look:

In [88]:
ZRI.head()
Out[88]:
RegionName Date ZRI
0 New York, NY 2010-11 NaN
1 Los Angeles-Long Beach-Anaheim, CA 2010-11 2283.0
2 Chicago, IL 2010-11 1581.0
3 Philadelphia, PA 2010-11 1557.0
4 Houston, TX 2010-11 1370.0
In [89]:
ZHVI.head()
Out[89]:
RegionName Date ZHVI
0 New York, NY 1996-04 165000.0
1 Los Angeles-Long Beach-Anaheim, CA 1996-04 170600.0
2 Chicago, IL 1996-04 138300.0
3 Philadelphia, PA 1996-04 109700.0
4 Houston, TX 1996-04 NaN

Note that missing data values are represented as NaN ("not a number")

Merging data frames

Another common operation is merging, also known as joining, two datasets.

We can use the merge() function to merge observations that have the same Date and RegionName values.

In [90]:
zillow_data = pd.merge(ZRI, ZHVI, on=['Date', 'RegionName'], how='outer')
In [91]:
zillow_data.head()
Out[91]:
RegionName Date ZRI ZHVI
0 New York, NY 2010-11 NaN 358800.0
1 Los Angeles-Long Beach-Anaheim, CA 2010-11 2283.0 410300.0
2 Chicago, IL 2010-11 1581.0 182800.0
3 Philadelphia, PA 2010-11 1557.0 204400.0
4 Houston, TX 2010-11 1370.0 137500.0

Merging is very powerful and the merge can be done in a number of ways. See the infographic on joining in this repository.

Datetime objects

Currently our Date column is stored as a string.

pandas includes additional functionality for dates, but first we must convert the strings using the to_datetime() function.

In [92]:
# convert to data
zillow_data['Date'] = pd.to_datetime(zillow_data['Date'])

Quick trick: Series that hold Datetime objects have a dt attribute that let's you grab parts of the date easily.

For example, we can easily add new columns for the month and year using:

In [93]:
# Note the the dtype is now datetime64[ns]
zillow_data['Date'].head()
Out[93]:
0   2010-11-01
1   2010-11-01
2   2010-11-01
3   2010-11-01
4   2010-11-01
Name: Date, dtype: datetime64[ns]
In [94]:
zillow_data['Month'] = zillow_data['Date'].dt.month
zillow_data['Year'] = zillow_data['Date'].dt.year
In [95]:
zillow_data.head()
Out[95]:
RegionName Date ZRI ZHVI Month Year
0 New York, NY 2010-11-01 NaN 358800.0 11 2010
1 Los Angeles-Long Beach-Anaheim, CA 2010-11-01 2283.0 410300.0 11 2010
2 Chicago, IL 2010-11-01 1581.0 182800.0 11 2010
3 Philadelphia, PA 2010-11-01 1557.0 204400.0 11 2010
4 Houston, TX 2010-11-01 1370.0 137500.0 11 2010

Annual trends: grouping by Year

pandas is especially useful for grouping and aggregating data via the groupby() function.

From the pandas documentation, groupby means:

  • Splitting the data into groups based on some criteria.
  • Applying a function to each group independently.
  • Combining the results into a data structure.

The documentation is available here.

We can calculate annual averages for each year by grouping by the RegionName and Year columns and taking the mean of our desired column. For example:

In [96]:
# calculate mean values for each Year and City (RegionName)
annual_ZHVI = zillow_data.groupby(['RegionName', 'Year'])['ZHVI'].mean() 
annual_ZRI = zillow_data.groupby(['RegionName', 'Year'])['ZRI'].mean()
In [97]:
print(type(annual_ZHVI))

<class 'pandas.core.series.Series'>
In [98]:
annual_ZHVI.head()
Out[98]:
RegionName   Year
Chicago, IL  1996    138344.444444
             1997    130316.666667
             1998    137841.666667
             1999    147516.666667
             2000    159900.000000
Name: ZHVI, dtype: float64

Note that here that the result is indexed by the columns we grouped by (RegionName and Year).

We can reset the index so that the index values are listed as columns in the data frame again.

In [99]:
annual_ZHVI = annual_ZHVI.reset_index()
annual_ZRI = annual_ZRI.reset_index()
In [100]:
annual_ZHVI.head(n=50)
Out[100]:
RegionName Year ZHVI
0 Chicago, IL 1996 138344.444444
1 Chicago, IL 1997 130316.666667
2 Chicago, IL 1998 137841.666667
3 Chicago, IL 1999 147516.666667
4 Chicago, IL 2000 159900.000000
5 Chicago, IL 2001 172858.333333
6 Chicago, IL 2002 185350.000000
7 Chicago, IL 2003 197341.666667
8 Chicago, IL 2004 216033.333333
9 Chicago, IL 2005 237100.000000
10 Chicago, IL 2006 251775.000000
11 Chicago, IL 2007 251450.000000
12 Chicago, IL 2008 233575.000000
13 Chicago, IL 2009 206675.000000
14 Chicago, IL 2010 190841.666667
15 Chicago, IL 2011 171133.333333
16 Chicago, IL 2012 161150.000000
17 Chicago, IL 2013 168450.000000
18 Chicago, IL 2014 179666.666667
19 Chicago, IL 2015 188175.000000
20 Chicago, IL 2016 197633.333333
21 Chicago, IL 2017 208983.333333
22 Chicago, IL 2018 220536.363636
23 Houston, TX 1996 NaN
24 Houston, TX 1997 105920.000000
25 Houston, TX 1998 111800.000000
26 Houston, TX 1999 121250.000000
27 Houston, TX 2000 126775.000000
28 Houston, TX 2001 124141.666667
29 Houston, TX 2002 125650.000000
30 Houston, TX 2003 128583.333333
31 Houston, TX 2004 135741.666667
32 Houston, TX 2005 141791.666667
33 Houston, TX 2006 144033.333333
34 Houston, TX 2007 148366.666667
35 Houston, TX 2008 144950.000000
36 Houston, TX 2009 140841.666667
37 Houston, TX 2010 139341.666667
38 Houston, TX 2011 135175.000000
39 Houston, TX 2012 134516.666667
40 Houston, TX 2013 139483.333333
41 Houston, TX 2014 148350.000000
42 Houston, TX 2015 164275.000000
43 Houston, TX 2016 178841.666667
44 Houston, TX 2017 188108.333333
45 Houston, TX 2018 198454.545455
46 Los Angeles-Long Beach-Anaheim, CA 1996 169722.222222
47 Los Angeles-Long Beach-Anaheim, CA 1997 172133.333333
48 Los Angeles-Long Beach-Anaheim, CA 1998 188033.333333
49 Los Angeles-Long Beach-Anaheim, CA 1999 207566.666667

Plotting our results: ZHVI

In [101]:
with plt.style.context("ggplot"):

    # Create figure and axes
    fig, ax = plt.subplots(figsize=(10, 6))
    
    # Plot for each unique city
    for city in annual_ZHVI["RegionName"].unique():
        
        # select the data for this city
        selection = annual_ZHVI["RegionName"] == city
        df = annual_ZHVI.loc[selection]

        # plot
        ax.plot(df["Year"], df["ZHVI"] / 1e3, label=city, linewidth=4)

    
    # Format the axes
    ax.set_ylim(50, 800)
    ax.legend(loc=0, ncol=2, fontsize=12)
    ax.set_ylabel("Zillow Home Value Index\n(in thousands of dollars)")

Home values in Philadelphia have only recently recovered to pre-2008 levels

Plotting the results: Zillow Rent Index

In [102]:
with plt.style.context('ggplot'):
    
    # Create the figure and axes
    fig, ax = plt.subplots(figsize=(10,6))
    
    # Loop over the cities to plot each one
    for city in annual_ZRI['RegionName'].unique():
        
        # Select the city data
        selection = annual_ZRI['RegionName'] == city
        df = annual_ZRI.loc[selection]
        
        # Plot
        ax.plot(df['Year'], df['ZRI'], label=city, linewidth=4)
    
    # Format
    ax.set_ylim(1000, 3300)
    ax.legend(loc=0, ncol=2)
    ax.set_ylabel('Zillow Rent Index (in dollars)')

Rent prices in Philadelphia have remained relatively flat, relative to other large cities.

First Homework Assignment

Available on GitHub:

https://github.com/MUSA-550-Fall-2020/assignment-1

Two parts:

  1. Download and install Python locally on your computer.
  2. Analyze Zillow ZHVI data by ZIP code in Philadelphia, submitting your Jupyter notebook

Due date: next Thursday 9/10 by the start of class (6pm)

That's it!

  • Stay tuned for announcement of office hours in the coming days
  • Post questions on Piazza
  • Email questions/concerns to nhand@design.upenn.edu